﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Hp.Vertica.Client.Internal
{
    public class Table
    {
        public Table() { }
        public Table(String name)
        {
            this.Name = name;
        }

        public Table(String name, Schema schema, Category category)
            : this(name)
        {
            this.Schema = schema;
            this.Category = category;
        }
        public String Name { get; set; }
        public String Remark { get; set; }

        public List<Column> Columns { get { return this.Schema.Database.GetColumns(this); } }

        public Schema Schema { get; set; }

        public Category Category { get; set; }

        public VerticaDatabase Database
        {
            get
            {
                return this.Schema.Database;
            }
        }

        public String FullName
        {
            get
            {
                return this.Schema.Name + "." + this.Name;
            }
        }

        public String SelectScript
        {
            get
            {
                StringBuilder sb = new StringBuilder("SELECT ");
                int index = 0;
                foreach (var col in this.Columns)
                {
                    if (index != 0)
                    {
                        sb.Append(",\n");
                    }
                    sb.Append("    ");
                    sb.Append(col.Name);
                    index++;
                }
                sb.Append("\nFROM ");
                sb.Append(this.FullName);
                sb.Append(";\n");
                return sb.ToString();
            }
        }

        public String SelectTop1000Script
        {
            get
            {
                StringBuilder sb = new StringBuilder("SELECT ");
                int index = 0;
                foreach (var col in this.Columns)
                {
                    if (index != 0)
                    {
                        sb.Append(",\n");
                    }
                    sb.Append("    ");
                    sb.Append(col.Name);
                    index++;
                }
                sb.Append("\nFROM ");
                sb.Append(this.FullName);
                sb.Append(" LIMIT 1000;\n");
                return sb.ToString();
            }
        }

        public String CreateScript
        {
            get
            {
                StringBuilder sb = new StringBuilder("CREATE TABLE ");
                sb.Append(this.FullName);
                sb.Append("\n(\n");
                int index = 0;
                foreach (var col in this.Columns)
                {
                    if (index != 0)
                    {
                        sb.Append(",\n");
                    }
                    sb.Append("    ");
                    sb.Append(col);
                    index++;
                }
                sb.Append("\n);\n");
                return sb.ToString();
            }
        }

        public String InsertScript
        {
            get
            {
                StringBuilder sb = new StringBuilder("INSERT INTO ");
                sb.Append(this.FullName);
                sb.Append("\n (\n");
                int index = 0;
                foreach (var col in this.Columns.Where(c => !c.IsIdentity))
                {
                    if (index != 0)
                    {
                        sb.Append(",\n");
                    }
                    sb.Append("    ");
                    sb.Append(col.Name);
                    index++;
                }
                sb.Append("\n)");
                sb.Append("\nVALUES (\n");
                index = 0;
                foreach (var col in this.Columns.Where(c => !c.IsIdentity))
                {
                    if (index != 0)
                    {
                        sb.Append(",\n");
                    }
                    sb.Append("    @");
                    sb.Append(col.Name);
                    index++;
                }
                sb.Append("\n);\n");
                return sb.ToString();
            }
        }

        public String DeleteScript
        {
            get
            {
                StringBuilder sb = new StringBuilder("DELETE FROM ");
                sb.Append(this.FullName);
                sb.Append("\nWHERE ");
                sb.Append("<ColumnName> = <Value>;\n");
                return sb.ToString();
            }
        }

        public String UpdateScript
        {
            get
            {
                StringBuilder sb = new StringBuilder("UPDATE ");
                sb.Append(this.FullName);
                sb.Append("\nSET <ColumnName> = <Value>\nWHERE <Expression>;\n");
                return sb.ToString();
            }
        }

        public String GetDeleteScriptBy(String colName)
        {
            StringBuilder sb = new StringBuilder("DELETE FROM ");
            sb.Append(this.FullName);
            sb.Append("\nWHERE ");
            sb.Append(colName);
            sb.Append(" = @");
            sb.Append(colName);
            sb.Append(";");
            return sb.ToString();
        }

        public static readonly Char ColumnDelimiter = '\t';
        public static readonly Char RowTerminator = '\n';

        public String BulkCopyScript
        {
            get
            {
                StringBuilder sb = new StringBuilder("COPY ");
                sb.Append(this.FullName);
                sb.Append("\n(\n");
                int index = 0;
                foreach (var col in this.Columns)
                {
                    if (index != 0)
                    {
                        sb.Append(",\n");
                    }
                    sb.Append("    ");
                    sb.Append(col.Name);
                    index++;
                }
                sb.Append("\n) FROM STDIN\nDELIMITER E'\\t'\nRECORD TERMINATOR E'\\n';\n");
                return sb.ToString();
            }
        }

    }
}
